﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Workflow.Common.Dao;
using Workflow.IDAO;
using Workflow.Model;
using Workflow.Dto;
using Workflow.Model.Enum;
using NHibernate.Linq;
using System.Linq.Dynamic;
using NHibernate.Transform;

namespace Workflow.DAO
{
	public class CaseEEMDao : RepositoryBase<CaseEEM>, ICaseEEMDao
	{
		/// <summary>
		/// 根据条件查询CaseEEM
		/// </summary>
		/// <param name="count"></param>
		/// <param name="pageIndex"></param>
		/// <param name="pageSize"></param>
		/// <param name="caseQueryDto"></param>
		/// <param name="orderBy"></param>
		/// <param name="sort"></param>
		/// <returns></returns>
		public IList<CaseEEM> GetAllCaseByPage(out long count, int pageIndex, int pageSize, CaseQueryDto caseQueryDto, string orderBy, string sort)
		{
			var result = Session.Query<CaseEEM>();

			if (caseQueryDto != null)
			{
				if (!string.IsNullOrEmpty(caseQueryDto.WorkStatus))
				{
					result = result.Where(x => x.WorkStatus.WorkStatusCode == caseQueryDto.WorkStatus);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.FileName))
				{
					result = result.Where(x => x.FileName.Contains(caseQueryDto.FileName));
				}
				//if (!string.IsNullOrEmpty(caseQueryDto.ProcessType))
				//{
				//    result = result.Where(x => x.ProcessType.FolderCode == caseQueryDto.ProcessType);
				//}
				if (!string.IsNullOrEmpty(caseQueryDto.FolderLevelA))
				{
					result = result.Where(x => x.FolderLevelA.FolderCode == caseQueryDto.FolderLevelA);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.FolderLevelB))
				{
					result = result.Where(x => x.FolderLevelB.FolderCode == caseQueryDto.FolderLevelB);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.OperatorId) && caseQueryDto.OperatorId != "0")
				{
					result = result.Where(x => x.Operator.UserID == Int64.Parse(caseQueryDto.OperatorId));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.InvoiceNo))
				{
					result = result.Where(x => x.InvoiceNo == caseQueryDto.InvoiceNo);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.ReceiveDateFrom))
				{
					result = result.Where(x => x.ReceiveDate >= DateTime.Parse(caseQueryDto.ReceiveDateFrom));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.ReceiveDateTo))
				{

					result = result.Where(x => x.ReceiveDate < DateTime.Parse(caseQueryDto.ReceiveDateTo).AddDays(1));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.CheckType))
				{

					result = result.Where(x => x.CheckType.FolderCode == caseQueryDto.CheckType);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.StatusOperatorID) && caseQueryDto.StatusOperatorID != "0")
				{
					result = result.Where(x => x.StatusOperator.UserID == Int64.Parse(caseQueryDto.StatusOperatorID));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.StatusDateFrom))
				{
					result = result.Where(x => x.StatusDate >= DateTime.Parse(caseQueryDto.StatusDateFrom));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.StatusDateTo))
				{
					result = result.Where(x => x.StatusDate <= DateTime.Parse(caseQueryDto.StatusDateTo));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.Payment))
				{
					result = result.Where(x => x.CaseMapPropertyEEM.Payment.PaymentId == caseQueryDto.Payment);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.CommentCode))
				{
					result = result.Where(x => x.CommentCode.Id == caseQueryDto.CommentCode);
				}
			}
			
			count = result.LongCount();


			if (sort == "Payment")
			{
				var tempList = result.Fetch(x => x.CaseMapPropertyEEM).ThenFetch(x => x.Payment).ToList();
				if (orderBy == "asc")
				{
					tempList = tempList.OrderBy(x => x.CaseMapPropertyEEM == null ? "" : x.CaseMapPropertyEEM.Payment == null ? "" : x.CaseMapPropertyEEM.Payment.PaymentName).ToList();
				}
				else
				{
					tempList = tempList.OrderByDescending(x => x.CaseMapPropertyEEM == null ? null : x.CaseMapPropertyEEM.Payment == null ? null : x.CaseMapPropertyEEM.Payment.PaymentName).ToList();

				}
				return tempList.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
			}
			else
			{
				if (!(string.IsNullOrEmpty(sort) || string.IsNullOrEmpty(orderBy)))
				{
					result = result.OrderBy(sort + " " + orderBy);
				}

				return result.Skip((pageIndex - 1)*pageSize).Take(pageSize).ToList<CaseEEM>();
			}
		}

		/// <summary>
		/// FolderClass根据父亲节点查询子节点
		/// </summary>
		/// <param name="parentCode"></param>
		/// <returns></returns>
		public IList<FolerClass> GetFolderClass(string parentCode)
		{
			var result = Session.Query<FolerClass>();

			result = result.Where(x => x.ParentCode == parentCode);
			result = result.Where(x => x.CaseType == "2");
			return result.ToList();
		}

		public IList<EEMCaseMonitorDto> GetCaseMonitor(CaseMonitorQueryDto caseMonitorQueryDto)
		{

			var eemCaseMonitorDtos = Session.GetNamedQuery("GetCaseMonitorEEMProc")
				.SetString(0, String.IsNullOrEmpty(caseMonitorQueryDto.WorkStatus) ? null : caseMonitorQueryDto.WorkStatus)
			.SetString(1, String.IsNullOrEmpty(caseMonitorQueryDto.ReceiveDate) ? null : caseMonitorQueryDto.ReceiveDate)
			.SetString(2, string.IsNullOrEmpty(caseMonitorQueryDto.CheckType) ? null : caseMonitorQueryDto.CheckType)
			.SetResultTransformer(
				Transformers.AliasToBean(typeof(EEMCaseMonitorDto))).List<EEMCaseMonitorDto>();


			return eemCaseMonitorDtos;
			
		}

		public IList<CaseEEM> GetCaseByUser(out long count, int pageIndex, int pageSize, CaseQueryDto caseQueryDto, string orderBy, string sort)
		{
			var result = Session.Query<CaseEEM>();

			if (caseQueryDto != null)
			{
				if (!string.IsNullOrEmpty(caseQueryDto.WorkStatus))
				{
					result = result.Where(x => x.WorkStatus.WorkStatusCode == caseQueryDto.WorkStatus);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.FileName))
				{
					result = result.Where(x => x.FileName.Contains(caseQueryDto.FileName));
				}
				//if (!string.IsNullOrEmpty(caseQueryDto.ProcessType))
				//{
				//    result = result.Where(x => x.ProcessType.FolderCode == caseQueryDto.ProcessType);
				//}
				if (!string.IsNullOrEmpty(caseQueryDto.FolderLevelA))
				{
					result = result.Where(x => x.FolderLevelA.FolderCode == caseQueryDto.FolderLevelA);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.FolderLevelB))
				{
					result = result.Where(x => x.FolderLevelB.FolderCode == caseQueryDto.FolderLevelB);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.OperatorId) && caseQueryDto.OperatorId != "0")
				{
					result = result.Where(x => x.Operator.UserID == Int64.Parse(caseQueryDto.OperatorId));

					//pa get
					result = result.Where(
						x =>
						x.WorkStatus.WorkStatusCode == ((int) EnumWorkStatus.Distributed).ToString() ||
						x.WorkStatus.WorkStatusCode == ((int) EnumWorkStatus.WaitForCheck).ToString() ||
						x.WorkStatus.WorkStatusCode == ((int) EnumWorkStatus.Pending_QA).ToString());
				}
				if (!string.IsNullOrEmpty(caseQueryDto.InvoiceNo))
				{
					result = result.Where(x => x.InvoiceNo == caseQueryDto.InvoiceNo);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.ReceiveDateFrom))
				{
					result = result.Where(x => x.ReceiveDate >= DateTime.Parse(caseQueryDto.ReceiveDateFrom));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.ReceiveDateTo))
				{

					result = result.Where(x => x.ReceiveDate < DateTime.Parse(caseQueryDto.ReceiveDateTo).AddDays(1));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.CheckType))
				{

					result = result.Where(x => x.CheckType.FolderCode == caseQueryDto.CheckType);
				}

			}

			count = result.LongCount();

			if (!(string.IsNullOrEmpty(sort) || string.IsNullOrEmpty(orderBy)))
			{
				result = result.OrderBy(sort + " " + orderBy);
			}

			return result.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList<CaseEEM>();
		}

		public IList<CaseEEM> GetAllCase(CaseQueryDto caseQueryDto)
		{
			var result = Session.Query<CaseEEM>();

			if (caseQueryDto != null)
			{
				if (!string.IsNullOrEmpty(caseQueryDto.WorkStatus))
				{
					result = result.Where(x => x.WorkStatus.WorkStatusCode == caseQueryDto.WorkStatus);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.FileName))
				{
					result = result.Where(x => x.FileName.Contains(caseQueryDto.FileName));
				}
				//if (!string.IsNullOrEmpty(caseQueryDto.ProcessType))
				//{
				//    result = result.Where(x => x.ProcessType.FolderCode == caseQueryDto.ProcessType);
				//}
				if (!string.IsNullOrEmpty(caseQueryDto.FolderLevelA))
				{
					result = result.Where(x => x.FolderLevelA.FolderCode == caseQueryDto.FolderLevelA);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.FolderLevelB))
				{
					result = result.Where(x => x.FolderLevelB.FolderCode == caseQueryDto.FolderLevelB);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.OperatorId) && caseQueryDto.OperatorId != "0")
				{
					result = result.Where(x => x.Operator.UserID == Int64.Parse(caseQueryDto.OperatorId));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.InvoiceNo))
				{
					result = result.Where(x => x.InvoiceNo == caseQueryDto.InvoiceNo);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.ReceiveDateFrom))
				{
					result = result.Where(x => x.ReceiveDate >= DateTime.Parse(caseQueryDto.ReceiveDateFrom));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.ReceiveDateTo))
				{

					result = result.Where(x => x.ReceiveDate < DateTime.Parse(caseQueryDto.ReceiveDateTo).AddDays(1));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.CheckType))
				{

					result = result.Where(x => x.CheckType.FolderCode == caseQueryDto.CheckType);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.StatusOperatorID) && caseQueryDto.StatusOperatorID != "0")
				{
					result = result.Where(x => x.StatusOperator.UserID == Int64.Parse(caseQueryDto.StatusOperatorID));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.StatusDateFrom))
				{
					result = result.Where(x => x.StatusDate >= DateTime.Parse(caseQueryDto.StatusDateFrom));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.StatusDateTo))
				{

					result = result.Where(x => x.StatusDate <= DateTime.Parse(caseQueryDto.StatusDateTo));
				}
				if (!string.IsNullOrEmpty(caseQueryDto.Payment))
				{
					result = result.Where(x => x.CaseMapPropertyEEM.Payment.PaymentId == caseQueryDto.Payment);
				}
				if (!string.IsNullOrEmpty(caseQueryDto.CommentCode))
				{
					result = result.Where(x => x.CommentCode.Id == caseQueryDto.CommentCode);
				}
			}

			return result.ToList<CaseEEM>();
		}

		/// <summary>
		/// 操作的下一个CaseEEM
		/// </summary>
		/// <param name="caseNo"></param>
		/// <param name="userid"></param>
		/// <returns></returns>
		public CaseEEM GetNextGase(long caseNo, long userid, string workStatus)
		{
			var result = Session.Query<CaseEEM>();
			result = result.Where(x => x.Operator.UserID ==  userid );
			if (workStatus == ((int)EnumWorkStatus.Distributed).ToString() || workStatus == ((int)EnumWorkStatus.Pending_QA).ToString())
			{
				result = result.Where(
				  x =>
				  x.WorkStatus.WorkStatusCode == ((int)EnumWorkStatus.Distributed).ToString() ||
				  x.WorkStatus.WorkStatusCode == ((int)EnumWorkStatus.Pending_QA).ToString());
			}
			if (workStatus == ((int)EnumWorkStatus.WaitForCheck).ToString())
			{
				result = result.Where(
				  x =>
				  x.WorkStatus.WorkStatusCode == ((int)EnumWorkStatus.WaitForCheck).ToString());
			} result = result.Where(x => x.CaseNo > caseNo);

			result.OrderBy(x => x.CaseNo);

			return result.FirstOrDefault();
		}

		/// <summary>
		/// 通过文件名去case
		/// </summary>
		/// <param name="fileName"></param>
		/// <returns></returns>
		public CaseEEM GetGaseByFileName(string fileName)
		{
			var result = Session.Query<CaseEEM>();

			result = result.Where(x => x.FileName == fileName);

			return result.FirstOrDefault();
		}

		public IList<EEMPAMonitorDto> GetPAMonitor(PAMonitorQueryDto paMonitorQueryDto)
		{

			var eemCaseMonitorDtos = Session.GetNamedQuery("GetPAMonitorEEMProc")
			.SetString(0, String.IsNullOrEmpty(paMonitorQueryDto.ReceiveDate) ? null : paMonitorQueryDto.ReceiveDate)
			.SetResultTransformer(
				Transformers.AliasToBean(typeof(EEMPAMonitorDto))).List<EEMPAMonitorDto>();

			return eemCaseMonitorDtos;
		}

		public IList<CycleTimeReportDto> GetCycleTimeReport(ReceivedCaseDayReportQueryDto ReceivedCaseDayReportQueryDto)
		{
			var from = ReceivedCaseDayReportQueryDto.FromDay;
			var to = ReceivedCaseDayReportQueryDto.ToDay;

			DateTime tempFrom = DateTime.Parse(from);
			var monthFrom = tempFrom.Year + "/" + tempFrom.Month + "/01";
			DateTime tempto = DateTime.Parse(to).AddDays(-1).AddMonths(1);
			var monthTo = tempto.Year + "/" + tempto.Month + "/01";

			StringBuilder sql = new StringBuilder();

			sql.Append(" WITH DATA AS ( ");
			sql.Append("     SELECT CONVERT(NVARCHAR(7), trrcp.ReceiveDate, 120)  ");
			sql.Append("            ReceivedDate, ");
			sql.Append("            SUM(trrcp.DEADLINE_ABOVE) Above , ");
			sql.Append("            SUM(trrcp.DEADLINE_FOLLOW) Follow ");
			sql.Append("     FROM   T_REPORT_CYCLETIME_EEM trrcp ");
			sql.Append(" WHERE  trrcp.ReceiveDate < '" + to + "' AND trrcp.ReceiveDate >= '" + from + "' ");

			sql.Append("     GROUP BY ");
			sql.Append("            CONVERT(NVARCHAR(7), trrcp.ReceiveDate, 120) ");
			sql.Append("  ), ");
			sql.Append(" dayShow AS( ");
			sql.Append("   SELECT CONVERT(VARCHAR(7), DATEADD(MONTH, number,'" + monthFrom + "'), 120) AS MonthDate ");
			sql.Append("    FROM   MASTER.dbo.spt_values ");
			sql.Append("    WHERE  TYPE = 'p' ");
			sql.Append("           AND DATEADD(MONTH, number, '" + monthFrom + "') < '" + monthTo + "' ");
			sql.Append("  ) ");
			sql.Append(" SELECT dayShow.MonthDate, ");
			sql.Append("       ISNULL(DATA.Above, 0) Above, ");
			sql.Append("       ISNULL(DATA.Follow, 0) Follow ");
			sql.Append(" FROM   dayShow ");
			sql.Append("       LEFT JOIN DATA ");
			sql.Append("            ON  dayShow.MonthDate = DATA.ReceivedDate ");

			return
				Session.CreateSQLQuery(sql.ToString()).SetResultTransformer(Transformers.AliasToBean(typeof(CycleTimeReportDto))).
					List<CycleTimeReportDto>();

		}

		public IList<ReceivedCaseDayReportEEMDto> ReceivedCaseDayReportEEM(ReceivedCaseDayReportQueryDto ReceivedCaseDayReportQueryDto)
		{


			var from = ReceivedCaseDayReportQueryDto.FromDay;
			var to = ReceivedCaseDayReportQueryDto.ToDay;
			var batchNo = ReceivedCaseDayReportQueryDto.BatchNo;

			StringBuilder sql = new StringBuilder();

			sql.Append(" WITH DATA AS ( ");
			sql.Append("     SELECT CONVERT(NVARCHAR(10), trrcp.RECEIVED_DATE, 120)  ");
			sql.Append("            ReceivedDate, ");
			sql.Append("            SUM(trrcp.COUNT_A) f719, ");
			sql.Append("            SUM(trrcp.COUNT_B) f718, ");
			sql.Append("            SUM(trrcp.COUNT_C) f819, ");
			sql.Append("            SUM(trrcp.COUNT_D) f818, ");
			sql.Append("            SUM(trrcp.COUNT_E) f919, ");
			sql.Append("            SUM(trrcp.COUNT_F) f1019, ");
			sql.Append("            SUM(trrcp.COUNT_G) f1018, ");
			sql.Append("            SUM(trrcp.COUNT_H) f1119, ");
			sql.Append("            SUM(trrcp.COUNT_I) f1121, ");
			sql.Append("            SUM(trrcp.COUNT_J) f1219, ");
			sql.Append("            SUM(trrcp.COUNT_K) f1218, ");
			sql.Append("            SUM(trrcp.COUNT_L) f1221, ");
			sql.Append("            SUM(trrcp.COUNT_M) f1319, ");
			sql.Append("            SUM(trrcp.COUNT_N) f1419, ");
			sql.Append("            SUM(trrcp.COUNT_O) f1418, ");
			sql.Append("            SUM(trrcp.COUNT_P) f1519, ");
			sql.Append("            SUM(trrcp.COUNT_Q) f1619, ");
			sql.Append("            SUM(trrcp.COUNT_R) f1618, ");
			sql.Append("            SUM(trrcp.COUNT_S) f1621 ");
			sql.Append("     FROM   T_REPORT_RECEIVED_CASE_EEM trrcp ");
			sql.Append(" WHERE  trrcp.RECEIVED_DATE < '" + to + "' AND trrcp.RECEIVED_DATE >= '" + from + "' ");
			if (!String.IsNullOrEmpty(batchNo))
			{
				sql.Append(" AND Right(trrcp.BACKUP_A,4) = '" + batchNo + "' ");
			}
			sql.Append("     GROUP BY ");
			sql.Append("            RECEIVED_DATE ");
			sql.Append("  ), ");
			sql.Append(" dayShow AS( ");
			sql.Append("   SELECT CONVERT(VARCHAR(10), DATEADD(DAY, number,'" + from + "'), 120) AS DayDate ");
			sql.Append("    FROM   MASTER.dbo.spt_values ");
			sql.Append("    WHERE  TYPE = 'p' ");
			sql.Append("           AND DATEADD(DAY, number, '" + from + "') < '" + to + "' ");
			sql.Append("  ) ");
			sql.Append(" SELECT dayShow.dayDate, ");
			sql.Append("       ISNULL(DATA.f719, 0) f719, ");
			sql.Append("       ISNULL(DATA.f718, 0) f718, ");
			sql.Append("       ISNULL(DATA.f819, 0) f819, ");
			sql.Append("       ISNULL(DATA.f818, 0) f818, ");
			sql.Append("       ISNULL(DATA.f919, 0) f919, ");
			sql.Append("       ISNULL(DATA.f1019, 0) f1019, ");
			sql.Append("       ISNULL(DATA.f1018, 0) f1018, ");
			sql.Append("       ISNULL(DATA.f1119, 0) f1119, ");
			sql.Append("       ISNULL(DATA.f1121, 0) f1121, ");
			sql.Append("       ISNULL(DATA.f1219, 0) f1219, ");
			sql.Append("       ISNULL(DATA.f1218, 0) f1218, ");
			sql.Append("       ISNULL(DATA.f1221, 0) f1221, ");
			sql.Append("       ISNULL(DATA.f1319, 0) f1319, ");
			sql.Append("       ISNULL(DATA.f1419, 0) f1419, ");
			sql.Append("       ISNULL(DATA.f1418, 0) f1418, ");
			sql.Append("       ISNULL(DATA.f1519, 0) f1519, ");
			sql.Append("       ISNULL(DATA.f1619, 0) f1619, ");
			sql.Append("       ISNULL(DATA.f1618, 0) f1618, ");
			sql.Append("       ISNULL(DATA.f1621, 0) f1621 ");
			sql.Append(" FROM   dayShow ");
			sql.Append("       LEFT JOIN DATA ");
			sql.Append("            ON  dayShow.dayDate = DATA.ReceivedDate ");


			return Session.CreateSQLQuery(sql.ToString()).
				SetResultTransformer(Transformers.AliasToBean(typeof(ReceivedCaseDayReportEEMDto)))
				.List<ReceivedCaseDayReportEEMDto>();
		}

		public IList<ReceivedCaseDayReportEEMDto> ReceivedCaseMonthReportEEM(ReceivedCaseDayReportQueryDto ReceivedCaseDayReportQueryDto)
		{


			var from = ReceivedCaseDayReportQueryDto.FromDay;
			var to = ReceivedCaseDayReportQueryDto.ToDay;
			var batchNo = ReceivedCaseDayReportQueryDto.BatchNo;

			DateTime tempFrom = DateTime.Parse(from);
			var monthFrom = tempFrom.Year + "/" + tempFrom.Month + "/01";
			DateTime tempto = DateTime.Parse(to).AddDays(-1).AddMonths(1);
			var monthTo = tempto.Year + "/" + tempto.Month + "/01";

			StringBuilder sql = new StringBuilder();

			sql.Append(" WITH DATA AS ( ");
			sql.Append("     SELECT CONVERT(NVARCHAR(7), trrcp.RECEIVED_DATE, 120)  ");
			sql.Append("            ReceivedDate, ");
			sql.Append("            SUM(trrcp.COUNT_A) f719, ");
			sql.Append("            SUM(trrcp.COUNT_B) f718, ");
			sql.Append("            SUM(trrcp.COUNT_C) f819, ");
			sql.Append("            SUM(trrcp.COUNT_D) f818, ");
			sql.Append("            SUM(trrcp.COUNT_E) f919, ");
			sql.Append("            SUM(trrcp.COUNT_F) f1019, ");
			sql.Append("            SUM(trrcp.COUNT_G) f1018, ");
			sql.Append("            SUM(trrcp.COUNT_H) f1119, ");
			sql.Append("            SUM(trrcp.COUNT_I) f1121, ");
			sql.Append("            SUM(trrcp.COUNT_J) f1219, ");
			sql.Append("            SUM(trrcp.COUNT_K) f1218, ");
			sql.Append("            SUM(trrcp.COUNT_L) f1221, ");
			sql.Append("            SUM(trrcp.COUNT_M) f1319, ");
			sql.Append("            SUM(trrcp.COUNT_N) f1419, ");
			sql.Append("            SUM(trrcp.COUNT_O) f1418, ");
			sql.Append("            SUM(trrcp.COUNT_P) f1519, ");
			sql.Append("            SUM(trrcp.COUNT_Q) f1619, ");
			sql.Append("            SUM(trrcp.COUNT_R) f1618, ");
			sql.Append("            SUM(trrcp.COUNT_S) f1621 ");
			sql.Append("     FROM   T_REPORT_RECEIVED_CASE_EEM trrcp ");
			sql.Append(" WHERE  trrcp.RECEIVED_DATE < '" + to + "' AND trrcp.RECEIVED_DATE >= '" + from + "' ");
			if (!String.IsNullOrEmpty(batchNo))
			{
				sql.Append(" AND Right(trrcp.BACKUP_A,4) = '" + batchNo + "' ");
			}
			sql.Append("     GROUP BY ");
			sql.Append("            CONVERT(NVARCHAR(7), trrcp.RECEIVED_DATE, 120) ");
			sql.Append("  ), ");
			sql.Append(" dayShow AS( ");
			sql.Append("   SELECT CONVERT(VARCHAR(7), DATEADD(MONTH, number,'" + monthFrom + "'), 120) AS DayDate ");
			sql.Append("    FROM   MASTER.dbo.spt_values ");
			sql.Append("    WHERE  TYPE = 'p' ");
			sql.Append("           AND DATEADD(MONTH, number, '" + monthFrom + "') < '" + monthTo + "' ");
			sql.Append("  ) ");
			sql.Append(" SELECT dayShow.dayDate, ");
			sql.Append("       ISNULL(DATA.f719, 0) f719, ");
			sql.Append("       ISNULL(DATA.f718, 0) f718, ");
			sql.Append("       ISNULL(DATA.f819, 0) f819, ");
			sql.Append("       ISNULL(DATA.f818, 0) f818, ");
			sql.Append("       ISNULL(DATA.f919, 0) f919, ");
			sql.Append("       ISNULL(DATA.f1019, 0) f1019, ");
			sql.Append("       ISNULL(DATA.f1018, 0) f1018, ");
			sql.Append("       ISNULL(DATA.f1119, 0) f1119, ");
			sql.Append("       ISNULL(DATA.f1121, 0) f1121, ");
			sql.Append("       ISNULL(DATA.f1219, 0) f1219, ");
			sql.Append("       ISNULL(DATA.f1218, 0) f1218, ");
			sql.Append("       ISNULL(DATA.f1221, 0) f1221, ");
			sql.Append("       ISNULL(DATA.f1319, 0) f1319, ");
			sql.Append("       ISNULL(DATA.f1419, 0) f1419, ");
			sql.Append("       ISNULL(DATA.f1418, 0) f1418, ");
			sql.Append("       ISNULL(DATA.f1519, 0) f1519, ");
			sql.Append("       ISNULL(DATA.f1619, 0) f1619, ");
			sql.Append("       ISNULL(DATA.f1618, 0) f1618, ");
			sql.Append("       ISNULL(DATA.f1621, 0) f1621 ");
			sql.Append(" FROM   dayShow ");
			sql.Append("       LEFT JOIN DATA ");
			sql.Append("            ON  dayShow.dayDate = DATA.ReceivedDate ");


			return Session.CreateSQLQuery(sql.ToString()).
				SetResultTransformer(Transformers.AliasToBean(typeof(ReceivedCaseDayReportEEMDto)))
				.List<ReceivedCaseDayReportEEMDto>();
		}
		
		public IList<UserWorkloadReportEEMDto> GetUserWorkloadReportEEM(ReceivedCaseDayReportQueryDto ReceivedCaseDayReportQueryDto)
		{

			var from = ReceivedCaseDayReportQueryDto.FromDay;
			var to = ReceivedCaseDayReportQueryDto.ToDay;

			DateTime tempFrom = DateTime.Parse(from);
			var monthFrom = tempFrom.Year + "/" + tempFrom.Month + "/01";
			DateTime tempto = DateTime.Parse(to).AddDays(-1).AddMonths(1);
			var monthTo = tempto.Year + "/" + tempto.Month + "/01";

			StringBuilder sql = new StringBuilder();

			sql.Append(" WITH detail AS ( ");
			sql.Append("                    SELECT CONVERT(NVARCHAR(7), workload.STATUS_DATE, 120)  ");
			sql.Append("                           DayDate, ");
			sql.Append("                           tu.UserID, ");
			sql.Append("                           tu.UserName, ");
			sql.Append("                           SUM(workload.WORKLOAD) WORKLOAD ");
			sql.Append("                    FROM   T_REPORT_USERWORKLOAD_EEM workload ");
			sql.Append("                           LEFT JOIN T_USER tu ");
			sql.Append("                                ON  workload.USER_ID = tu.UserID ");
			sql.Append("					WHERE  workload.STATUS_DATE < '" + to + "' AND workload.STATUS_DATE >= '" + from + "' ");
			sql.Append("                    GROUP BY ");
			sql.Append("                           CONVERT(NVARCHAR(7), workload.STATUS_DATE, 120), ");
			sql.Append("                           tu.UserID, ");
			sql.Append("                           tu.UserName ");
			sql.Append("                ),    ");
			sql.Append(" dayShow AS( ");
			sql.Append("   SELECT CONVERT(VARCHAR(7), DATEADD(Month, number,'" + monthFrom + "'), 120) AS DayDate ");
			sql.Append("    ,tu.UserID,tu.UserName ");
			sql.Append("    FROM   MASTER.dbo.spt_values,T_USER tu ");
			sql.Append("    WHERE  TYPE = 'p' ");
			sql.Append("           AND DATEADD(Month, number, '" + monthFrom + "') < '" + monthTo + "' ");
			sql.Append("           AND  tu.GroupCode IN ('3','9')");
			sql.Append("           AND tu.RoleCode IN ('7','27')");
			sql.Append("           AND tu.IsValid='1' ");
			sql.Append("           ) ");
			sql.Append(" SELECT dayShow.DayDate, ");
			sql.Append("        dayShow.UserID, ");
			sql.Append("        dayShow.UserName, ");
			sql.Append("        ISNULL(detail.WORKLOAD,0) WORKLOAD ");
			sql.Append(" FROM   dayShow ");
			sql.Append("        LEFT JOIN detail ");
			sql.Append("             ON  dayShow.DayDate = detail.DayDate ");
			sql.Append("             AND  dayShow.UserID = detail.UserID ");
			sql.Append(" ORDER BY ");
			sql.Append("       dayShow.DayDate, ");
			sql.Append("        detail.UserID ");

			return Session.CreateSQLQuery(sql.ToString()).
				SetResultTransformer(Transformers.AliasToBean(typeof(UserWorkloadReportEEMDto)))
				.List<UserWorkloadReportEEMDto>();


		}

		public IList<FirstPassYeildReportEEMDto> GetFirstPassYeildReportEEM(ReceivedCaseDayReportQueryDto ReceivedCaseDayReportQueryDto)
		{

			var from = ReceivedCaseDayReportQueryDto.FromDay;
			var to = ReceivedCaseDayReportQueryDto.ToDay;

			DateTime tempFrom = DateTime.Parse(from);
			var monthFrom = tempFrom.Year + "/" + tempFrom.Month + "/01";
			DateTime tempto = DateTime.Parse(to).AddDays(-1).AddMonths(1);
			var monthTo = tempto.Year + "/" + tempto.Month + "/01";


			StringBuilder sql = new StringBuilder();

			sql.Append(" WITH DATA AS ( ");
			sql.Append("                  SELECT CONVERT(NVARCHAR(7), trfp.RECEIVED_DATE, 120)  ");
			sql.Append("                         ReceivedDate, ");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_A2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_A1)) /sum(trfp.COUNT_A2)) ");
			sql.Append("   END f719Rate, SUM(trfp.COUNT_A1) f719Pass ,sum(trfp.COUNT_A2) f719All,");
			sql.Append("  CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_B2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_B1)) /sum(trfp.COUNT_B2)) ");
			sql.Append("   END f718Rate, SUM(trfp.COUNT_B1) f718Pass,sum(trfp.COUNT_B2) f718All , ");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_C2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_C1)) /sum(trfp.COUNT_C2)) ");
			sql.Append("   END f819Rate, SUM(trfp.COUNT_C1) f819Pass,sum(trfp.COUNT_C2) f819All, ");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_D2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_D1)) /sum(trfp.COUNT_D2)) ");
			sql.Append("   END f818Rate, SUM(trfp.COUNT_D1) f818Pass,sum(trfp.COUNT_D2) f818All, ");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_E2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_E1)) /sum(trfp.COUNT_E2)) ");
			sql.Append("   END f919Rate, SUM(trfp.COUNT_E1) f919Pass,sum(trfp.COUNT_E2) f919All, ");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_F2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_F1)) /sum(trfp.COUNT_F2)) ");
			sql.Append("   END f1019Rate,  SUM(trfp.COUNT_F1) f1019Pass,sum(trfp.COUNT_F2) f1019All,");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_G2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_G1)) /sum(trfp.COUNT_G2)) ");
			sql.Append("   END f1018Rate, SUM(trfp.COUNT_G1) f1018Pass ,sum(trfp.COUNT_G2) f1018All, ");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_H2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_H1)) /sum(trfp.COUNT_H2)) ");
			sql.Append("   END f1119Rate, SUM(trfp.COUNT_H1) f1119Pass,sum(trfp.COUNT_H2) f1119All, ");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_I2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_I1)) /sum(trfp.COUNT_I2)) ");
			sql.Append("   END f1121Rate,  SUM(trfp.COUNT_I1) f1121Pass,sum(trfp.COUNT_I2) f1121All,");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_J2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_J1)) /sum(trfp.COUNT_J2)) ");
			sql.Append("   END f1219Rate,  SUM(trfp.COUNT_J1) f1219Pass,sum(trfp.COUNT_J2) f1219All,");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_K2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_K1)) /sum(trfp.COUNT_K2)) ");
			sql.Append("   END f1218Rate,  SUM(trfp.COUNT_K1) f1218Pass,sum(trfp.COUNT_K2) f1218All,");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_L2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_L1)) /sum(trfp.COUNT_L2)) ");
			sql.Append("   END f1221Rate,  SUM(trfp.COUNT_L1) f1221Pass,sum(trfp.COUNT_L2) f1221All,");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_M2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_M1)) /sum(trfp.COUNT_M2)) ");
			sql.Append("   END f1319Rate, SUM(trfp.COUNT_M1) f1319Pass,sum(trfp.COUNT_M2) f1319All, ");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_N2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_N1)) /sum(trfp.COUNT_N2)) ");
			sql.Append("   END f1419Rate,  SUM(trfp.COUNT_N1) f1419Pass,sum(trfp.COUNT_N2) f1419All,");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_O2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_O1)) /sum(trfp.COUNT_O2)) ");
			sql.Append("   END f1418Rate, SUM(trfp.COUNT_O1) f1418Pass,sum(trfp.COUNT_O2) f1418All, ");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_P2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_P1)) /sum(trfp.COUNT_P2)) ");
			sql.Append("   END f1519Rate,  SUM(trfp.COUNT_P1) f1519Pass,sum(trfp.COUNT_P2) f1519All,");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_Q2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_Q1)) /sum(trfp.COUNT_Q2)) ");
			sql.Append("   END f1619Rate,  SUM(trfp.COUNT_Q1) f1619Pass,sum(trfp.COUNT_Q2) f1619All,");
			sql.Append("   CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_R2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_R1)) /sum(trfp.COUNT_R2)) ");
			sql.Append("   END f1618Rate,  SUM(trfp.COUNT_R1) f1618Pass,sum(trfp.COUNT_R2) f1618All,");
			sql.Append("  CASE  ");
			sql.Append("        WHEN sum(trfp.COUNT_S2) = 0 THEN 0 ");
			sql.Append("        ELSE CONVERT(numeric(5, 4) ,CONVERT(FLOAT,SUM(trfp.COUNT_S1)) /sum(trfp.COUNT_S2)) ");
			sql.Append("   END f1621Rate , SUM(trfp.COUNT_S1) f1621Pass,sum(trfp.COUNT_S2) f1621All ");
			sql.Append("                  FROM   T_REPORT_FIRSTPASSYEILD_EEM trfp");
			sql.Append(" WHERE  trfp.RECEIVED_DATE < '" + to + "' AND trfp.RECEIVED_DATE >= '" + from + "' ");
			sql.Append("      GROUP BY  CONVERT(NVARCHAR(7), trfp.RECEIVED_DATE, 120) ");
			sql.Append("  ), ");
			sql.Append(" dayShow AS( ");
			sql.Append("   SELECT CONVERT(VARCHAR(7), DATEADD(Month, number,'" + monthFrom + "'), 120) AS DayDate ");
			sql.Append("    FROM   MASTER.dbo.spt_values ");
			sql.Append("    WHERE  TYPE = 'p' ");
			sql.Append("           AND DATEADD(Month, number, '" + monthFrom + "') < '" + monthTo + "' ");
			sql.Append("           ) ");
			sql.Append(" SELECT dayShow.DayDate, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f719Rate, 0)) f719Rate, ");
			sql.Append(" ISNULL(DATA.f719Pass, 0) f719Pass, ");
			sql.Append(" ISNULL(DATA.f719All, 0) f719All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f718Rate, 0)) f718Rate, ");
			sql.Append(" ISNULL(DATA.f718Pass, 0) f718Pass, ");
			sql.Append(" ISNULL(DATA.f718All, 0) f718All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f819Rate, 0)) f819Rate, ");
			sql.Append(" ISNULL(DATA.f819Pass, 0) f819Pass, ");
			sql.Append(" ISNULL(DATA.f819All, 0) f819All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f818Rate, 0)) f818Rate, ");
			sql.Append(" ISNULL(DATA.f818Pass, 0) f818Pass, ");
			sql.Append(" ISNULL(DATA.f818All, 0) f818All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f919Rate, 0)) f919Rate, ");
			sql.Append(" ISNULL(DATA.f919Pass, 0) f919Pass, ");
			sql.Append(" ISNULL(DATA.f919All, 0) f919All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1019Rate, 0)) f1019Rate, ");
			sql.Append(" ISNULL(DATA.f1019Pass, 0)  f1019Pass, ");
			sql.Append(" iSNULL(DATA.f1019All, 0) f1019All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1018Rate, 0)) f1018Rate, ");
			sql.Append(" ISNULL(DATA.f1018Pass, 0) f1018Pass, ");
			sql.Append(" ISNULL(DATA.f1018All, 0) f1018All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1119Rate, 0)) f1119Rate, ");
			sql.Append(" ISNULL(DATA.f1119Pass, 0) f1119Pass, ");
			sql.Append(" ISNULL(DATA.f1119All, 0) f1119All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1121Rate, 0)) f1121Rate, ");
			sql.Append(" ISNULL(DATA.f1121Pass, 0) f1121Pass, ");
			sql.Append(" ISNULL(DATA.f1121All, 0) f1121All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1219Rate, 0)) f1219Rate, ");
			sql.Append(" ISNULL(DATA.f1219Pass, 0) f1219Pass, ");
			sql.Append(" ISNULL(DATA.f1219All, 0) f1219All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1218Rate, 0)) f1218Rate, ");
			sql.Append(" ISNULL(DATA.f1218Pass, 0) f1218Pass, ");
			sql.Append(" ISNULL(DATA.f1218All, 0) f1218All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1221Rate, 0)) f1221Rate, ");
			sql.Append(" ISNULL(DATA.f1221Pass, 0) f1221Pass, ");
			sql.Append(" ISNULL(DATA.f1221All, 0) f1221All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1319Rate, 0)) f1319Rate, ");
			sql.Append(" ISNULL(DATA.f1319Pass, 0) f1319Pass, ");
			sql.Append(" ISNULL(DATA.f1319All, 0) f1319All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1419Rate, 0)) f1419Rate, ");
			sql.Append(" ISNULL(DATA.f1419Pass, 0) f1419Pass, ");
			sql.Append(" ISNULL(DATA.f1419All, 0) f1419All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1418Rate, 0)) f1418Rate, ");
			sql.Append(" ISNULL(DATA.f1418Pass, 0) f1418Pass, ");
			sql.Append(" ISNULL(DATA.f1418All, 0) f1418All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1519Rate, 0)) f1519Rate, ");
			sql.Append(" ISNULL(DATA.f1519Pass, 0) f1519Pass, ");
			sql.Append(" ISNULL(DATA.f1519All, 0) f1519All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1619Rate, 0)) f1619Rate, ");
			sql.Append(" ISNULL(DATA.f1619Pass, 0) f1619Pass, ");
			sql.Append(" ISNULL(DATA.f1619All, 0) f1619All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1618Rate, 0)) f1618Rate, ");
			sql.Append(" ISNULL(DATA.f1618Pass, 0) f1618Pass, ");
			sql.Append(" ISNULL(DATA.f1618All, 0) f1618All, ");
			sql.Append(" CONVERT(NUMERIC(5, 4), ISNULL(DATA.f1621Rate, 0)) f1621Rate, ");
			sql.Append(" ISNULL(DATA.f1621Pass, 0) f1621Pass, ");
			sql.Append(" ISNULL(DATA.f1621All, 0) f1621All ");
			sql.Append(" FROM   dayShow ");
			sql.Append("        LEFT JOIN DATA ");
			sql.Append("             ON  dayShow.dayDate = DATA.ReceivedDate ");

			return Session.CreateSQLQuery(sql.ToString()).
				SetResultTransformer(Transformers.AliasToBean(typeof (FirstPassYeildReportEEMDto)))
				.List<FirstPassYeildReportEEMDto>();

		}

		public IList<ReasonReportEEMDto> GetReasonReportEEM(ReceivedCaseDayReportQueryDto ReceivedCaseDayReportQueryDto)
		{
			var from = ReceivedCaseDayReportQueryDto.FromDay;
			var to = ReceivedCaseDayReportQueryDto.ToDay;
			var folderLevelA = ReceivedCaseDayReportQueryDto.FolderLevelA;
			var folderLevelB = ReceivedCaseDayReportQueryDto.FolderLevelB;
			
			StringBuilder sql = new StringBuilder();

			sql.Append(" WITH DATA AS ( ");
			sql.Append("                  SELECT * ");
			sql.Append("                  FROM   ( ");
			sql.Append("                             SELECT 'RL' c_code, ");
			sql.Append("                                    1 ORDERBY ");
			sql.Append("                             UNION SELECT 'MN' c_code, ");
			sql.Append("                                          2 ORDERBY ");
			sql.Append("                             UNION SELECT 'MS' c_code, ");
			sql.Append("                                          3 ORDERBY ");
			sql.Append("                             UNION SELECT 'SF' c_code, ");
			sql.Append("                                          4 ORDERBY ");
			sql.Append("                             UNION SELECT 'HH' c_code, ");
			sql.Append("                                          5 ORDERBY ");
			sql.Append("                             UNION SELECT '20' c_code, ");
			sql.Append("                                          6 ORDERBY ");
			sql.Append("                             UNION SELECT '21' c_code, ");
			sql.Append("                                          7 ORDERBY ");
			sql.Append("                             UNION SELECT '22' c_code, ");
			sql.Append("                                          8 ORDERBY ");
			sql.Append("                             UNION SELECT '23' c_code, ");
			sql.Append("                                          9 ORDERBY ");
			sql.Append("                             UNION SELECT '24' c_code, ");
			sql.Append("                                          10 ORDERBY ");
			sql.Append("                             UNION SELECT '25' c_code, ");
			sql.Append("                                          11 ORDERBY ");
			sql.Append("                             UNION SELECT '30' c_code, ");
			sql.Append("                                          12 ORDERBY ");
			sql.Append("                             UNION SELECT '31' c_code, ");
			sql.Append("                                          13 ORDERBY ");
			sql.Append("                             UNION SELECT '32' c_code, ");
			sql.Append("                                          14 ORDERBY ");
			sql.Append("                             UNION SELECT '33' c_code, ");
			sql.Append("                                          15 ORDERBY ");
			sql.Append("                             UNION SELECT '34' c_code, ");
			sql.Append("                                          16 ORDERBY ");
			sql.Append("                             UNION SELECT '35' c_code, ");
			sql.Append("                                          17 ORDERBY ");
			sql.Append("                             UNION SELECT '40' c_code, ");
			sql.Append("                                          18 ORDERBY ");
			sql.Append("                             UNION SELECT '41' c_code, ");
			sql.Append("                                          19 ORDERBY ");
			sql.Append("                             UNION SELECT '42' c_code, ");
			sql.Append("                                          20 ORDERBY ");
			sql.Append("                             UNION SELECT '43' c_code, ");
			sql.Append("                                          21 ORDERBY ");
			sql.Append("                             UNION SELECT '44' c_code, ");
			sql.Append("                                          22 ORDERBY ");
			sql.Append("                             UNION SELECT '45' c_code, ");
			sql.Append("                                          23 ORDERBY ");
			sql.Append("                             UNION SELECT '46' c_code, ");
			sql.Append("                                          24 ORDERBY ");
			sql.Append("                             UNION SELECT '47' c_code, ");
			sql.Append("                                          25 ORDERBY ");
			sql.Append("                             UNION SELECT '50' c_code, ");
			sql.Append("                                          26 ORDERBY ");
			sql.Append("                             UNION SELECT '51' c_code, ");
			sql.Append("                                          27 ORDERBY ");
			sql.Append("                             UNION SELECT '52' c_code, ");
			sql.Append("                                          28 ORDERBY ");
			sql.Append("                             UNION SELECT '53' c_code, ");
			sql.Append("                                          29 ORDERBY ");
			sql.Append("                             UNION SELECT '54' c_code, ");
			sql.Append("                                          30 ORDERBY ");
			sql.Append("                             UNION SELECT '55' c_code, ");
			sql.Append("                                          31 ORDERBY ");
			sql.Append("                             UNION SELECT '56' c_code, ");
			sql.Append("                                          32 ORDERBY ");
			sql.Append("                             UNION SELECT '57' c_code, ");
			sql.Append("                                          33 ORDERBY ");
			sql.Append("                             UNION SELECT '58' c_code, ");
			sql.Append("                                          34 ORDERBY ");
			sql.Append("                             UNION SELECT '60' c_code, ");
			sql.Append("                                          35 ORDERBY ");
			sql.Append("                             UNION SELECT '61' c_code, ");
			sql.Append("                                          36 ORDERBY ");
			sql.Append("                             UNION SELECT '62' c_code, ");
			sql.Append("                                          37 ORDERBY ");
			sql.Append("                             UNION SELECT '63' c_code, ");
			sql.Append("                                          38 ORDERBY ");
			sql.Append("                             UNION SELECT '64' c_code, ");
			sql.Append("                                          39 ORDERBY ");
			sql.Append("                             UNION SELECT '65' c_code, ");
			sql.Append("                                          40 ORDERBY ");
			sql.Append("                             UNION SELECT '66' c_code, ");
			sql.Append("                                          41 ORDERBY ");
			sql.Append("                             UNION SELECT '67' c_code, ");
			sql.Append("                                          42 ORDERBY ");
			sql.Append("                             UNION SELECT '70' c_code, ");
			sql.Append("                                          43 ORDERBY ");
			sql.Append("                             UNION SELECT '71' c_code, ");
			sql.Append("                                          44 ORDERBY ");
			sql.Append("                             UNION SELECT '72' c_code, ");
			sql.Append("                                          45 ORDERBY ");
			sql.Append("                             UNION SELECT '73' c_code, ");
			sql.Append("                                          46 ORDERBY ");
			sql.Append("                             UNION SELECT '74' c_code, ");
			sql.Append("                                          47 ORDERBY ");
			sql.Append("                             UNION SELECT '75' c_code, ");
			sql.Append("                                          48 ORDERBY ");
			sql.Append("                             UNION SELECT 'VA' c_code, ");
			sql.Append("                                          49 ORDERBY ");
			sql.Append("                             UNION SELECT 'VB' c_code, ");
			sql.Append("                                          50 ORDERBY ");
			sql.Append("                             UNION SELECT 'VE' c_code, ");
			sql.Append("                                          51 ORDERBY ");
			sql.Append("                             UNION SELECT 'VW' c_code, ");
			sql.Append("                                          52 ORDERBY ");
			sql.Append("                             UNION SELECT '' c_code, ");
			sql.Append("                                          53 ORDERBY ");
			sql.Append("                         ) main, ");
			sql.Append("                         T_CASE_REASON tcr ");
			sql.Append("                  WHERE  tcr.CASE_TYPE = '2' ");
			sql.Append("              ) ");
			sql.Append(" SELECT DATA.C_CODE, ");
			sql.Append("        DATA.REASON_ID, ");
			sql.Append("        DATA.JP_REASON, ");
			sql.Append("        DATA.REASON_TYPE, ");
			sql.Append("        isnull(sum(trre.Number ),0) NUM ");
			sql.Append(" FROM   DATA ");
			sql.Append("        LEFT JOIN T_REPORT_REASON_EEM trre ");
			sql.Append("             ON  DATA.REASON_ID = trre.CommentCode ");
			sql.Append("             AND DATA.c_code = (CASE WHEN   trre.InvoiceNo IN('RL','MN','MS','SF','HH','20','21','22','23','24','25','30','31'");
            sql.Append("             ,'32','33','34','35','40','41','42','43','44','45','46','47','50','51','52','53','54','55','56','57','58','60','61','62','63','64','65','66'");
			sql.Append("             ,'67','70','71','72','73','74','75','VA','VB','VE','VW') THEN trre.InvoiceNo");
			sql.Append("             					ELSE '' END   ) ");
			sql.Append("			and  trre.RECEIVED_DATE < '" + to + "' AND trre.RECEIVED_DATE >= '" + from + "' ");
			if (!string.IsNullOrEmpty(folderLevelA))
			{
				sql.Append("			and  trre.FolderLevel_A = '" + folderLevelA + "'  ");
			}
			if (!string.IsNullOrEmpty(folderLevelB))
			{
				sql.Append("			and  trre.FolderLevel_B = '" + folderLevelB + "' ");
			}
			
			sql.Append(" GROUP BY ");
			sql.Append("        DATA.C_CODE, ");
			sql.Append("        DATA.ORDERBY, ");
			sql.Append("        DATA.REASON_ID, ");
			sql.Append("        DATA.JP_REASON, ");
			sql.Append("        DATA.REASON_TYPE, ");
			sql.Append("        DATA.REASON_ORDER ");
			sql.Append(" ORDER BY ");
			sql.Append("        DATA.ORDERBY, ");
			sql.Append("        DATA.C_CODE, ");
			sql.Append("        DATA.REASON_TYPE desc, ");
			sql.Append("        DATA.REASON_ORDER ");

			return Session.CreateSQLQuery(sql.ToString()).
				SetResultTransformer(Transformers.AliasToBean(typeof (ReasonReportEEMDto)))
				.List<ReasonReportEEMDto>();
		}

		public IList<BaseDataReportEEMDto> GetBaseDataReportEEM(CaseQueryDto caseQueryDto)
		{
			StringBuilder sql = new StringBuilder();
			sql.Append(" SELECT tce.CaseNo, ");
			sql.Append(" tce.BatchNo, ");
			sql.Append(" tce.InvoiceNo, ");
			sql.Append(" tce.[FileName], ");
			sql.Append(" tdfc.FolderName FolderLevelA, ");
			sql.Append(" tdfc2.FolderName FolderLevelB, ");
			sql.Append(" tdfc3.FolderName CheckType, ");
			sql.Append(" tce.ReceiveDate, ");
			sql.Append(" tdw.WORKSTATUS_NAME_EEM, ");
			sql.Append(" tce.StatusDate, ");
			sql.Append(" tu.UserName StatusOperator, ");
			sql.Append(" tce.StatusComment, ");
			sql.Append(" tu2.UserName OperatorID, ");
			sql.Append(" tdp.PAYMENT_NAME, ");
			sql.Append(" tcr.JP_REASON ");
			sql.Append(" FROM T_CASE_EEM tce ");
			sql.Append(" LEFT JOIN T_DIC_FOLDER_CLASS tdfc ON tce.FolderLevel_A = tdfc.FolderCode ");
			sql.Append(" LEFT JOIN T_DIC_FOLDER_CLASS tdfc2 ON tce.FolderLevel_B = tdfc2.FolderCode ");
			sql.Append(" LEFT JOIN T_DIC_FOLDER_CLASS tdfc3 ON tce.CheckType = tdfc3.FolderCode ");
			sql.Append(" LEFT JOIN T_DIC_WORKSTATUS tdw  ON tdw.WORKSTATUS_CODE = tce.WorkStatus_Code ");
			sql.Append(" LEFT JOIN T_USER tu ON tce.StatusOperator= tu.UserID ");
			sql.Append(" LEFT JOIN T_USER tu2 ON tce.OperatorID = tu2.UserID ");
			sql.Append(" LEFT JOIN T_CASE_REASON tcr ON tce.CommentCode = tcr.REASON_ID ");
			sql.Append(" LEFT JOIN T_CASE_MAP_PROPERTY_EEM tcmpe ON tce.CaseNo = tcmpe.CaseNo ");
			sql.Append(" LEFT JOIN T_DIC_PAYMENT tdp ON tdp.PAYMENT_ID = tcmpe.PAYMENT_ID ");
			sql.Append(" WHERE 1=1 ");
			if (caseQueryDto != null)
			{
				if (!string.IsNullOrEmpty(caseQueryDto.WorkStatus))
				{
					sql.Append(" AND tdw.WORKSTATUS_CODE =  '" + caseQueryDto.WorkStatus + "'");
				}
				if (!string.IsNullOrEmpty(caseQueryDto.FileName))
				{
					sql.Append(" AND tce.[FileName] LIKE '%" + caseQueryDto.FileName + "%'");
				}
				
				if (!string.IsNullOrEmpty(caseQueryDto.FolderLevelA))
				{
					sql.Append(" AND tdfc.FolderCode = '" + caseQueryDto.FolderLevelA + "'");
				}
				if (!string.IsNullOrEmpty(caseQueryDto.FolderLevelB))
				{
					sql.Append(" AND tdfc2.FolderCode = '" + caseQueryDto.FolderLevelB + "'");
				}
				if (!string.IsNullOrEmpty(caseQueryDto.CheckType))
				{
					sql.Append(" AND tdfc3.FolderCode = '" + caseQueryDto.CheckType + "'");
				}
				if (!string.IsNullOrEmpty(caseQueryDto.OperatorId) && caseQueryDto.OperatorId != "0")
				{
					sql.Append(" AND tu2.UserID = '" + caseQueryDto.OperatorId + "'");
				}
				if (!string.IsNullOrEmpty(caseQueryDto.InvoiceNo))
				{
					sql.Append(" AND tce.InvoiceNo = '" + caseQueryDto.InvoiceNo + "'");
				}
				if (!string.IsNullOrEmpty(caseQueryDto.ReceiveDateFrom))
				{
					sql.Append(" AND tce.ReceiveDate >= '" + DateTime.Parse(caseQueryDto.ReceiveDateFrom) + "'");
				}
				if (!string.IsNullOrEmpty(caseQueryDto.ReceiveDateTo))
				{
					sql.Append(" AND tce.ReceiveDate < '" + DateTime.Parse(caseQueryDto.ReceiveDateTo).AddDays(1) + "'");
				}
				
				if (!string.IsNullOrEmpty(caseQueryDto.StatusOperatorID) && caseQueryDto.StatusOperatorID != "0")
				{
					sql.Append(" AND tu.UserID = '" + caseQueryDto.StatusOperatorID + "'");
				}
				if (!string.IsNullOrEmpty(caseQueryDto.StatusDateFrom))
				{
					sql.Append(" AND tce.StatusDate >= '" + DateTime.Parse(caseQueryDto.StatusDateFrom) + "'");
				}
				if (!string.IsNullOrEmpty(caseQueryDto.StatusDateTo))
				{
					sql.Append(" AND tce.StatusDate <= '" + DateTime.Parse(caseQueryDto.StatusDateTo) + "'");
	
				}
				if (!string.IsNullOrEmpty(caseQueryDto.Payment))
				{
					sql.Append(" AND tdp.PAYMENT_ID = '" + caseQueryDto.Payment + "'");
				}
				if (!string.IsNullOrEmpty(caseQueryDto.CommentCode))
				{
					sql.Append(" AND tcr.REASON_ID = '" + caseQueryDto.CommentCode + "'");
				}
			}
			return Session.CreateSQLQuery(sql.ToString()).
				SetResultTransformer(Transformers.AliasToBean(typeof (BaseDataReportEEMDto)))
				.List<BaseDataReportEEMDto>();
		}

		public IList<CycleTimeNewReportDto> GetCycleTimeNewReport(ReceivedCaseDayReportQueryDto ReceivedCaseDayReportQueryDto)
		{
			var from = ReceivedCaseDayReportQueryDto.FromDay;
			var to = ReceivedCaseDayReportQueryDto.ToDay;

			DateTime tempFrom = DateTime.Parse(from);
			var monthFrom = tempFrom.Year + "/" + tempFrom.Month + "/01";
			DateTime tempto = DateTime.Parse(to).AddDays(-1).AddMonths(1);
			var monthTo = tempto.Year + "/" + tempto.Month + "/01";

			StringBuilder sql = new StringBuilder();

			sql.Append(" WITH DATA AS ( ");
			sql.Append("     SELECT CONVERT(NVARCHAR(7), trcen.ReceiveDate, 120) ReceiveDate,");
			sql.Append("            work_day, ");
			sql.Append("            SUM(work_num)  work_num");
			sql.Append("     FROM   T_REPORT_CYCLETIME_EEM_NEW trcen ");
			sql.Append(" WHERE  trcen.ReceiveDate < '" + to + "' AND trcen.ReceiveDate >= '" + from + "' ");
			sql.Append("     GROUP BY ");
			sql.Append("            CONVERT(NVARCHAR(7), trcen.ReceiveDate, 120), ");
			sql.Append("            trcen.Work_Day ");
			sql.Append("  ), ");
			sql.Append(" dayShow AS( ");
			sql.Append("   SELECT CONVERT(VARCHAR(7), DATEADD(MONTH, number,'" + monthFrom + "'), 120) AS MonthDate ");
			sql.Append("    FROM   MASTER.dbo.spt_values ");
			sql.Append("    WHERE  TYPE = 'p' ");
			sql.Append("           AND DATEADD(MONTH, number, '" + monthFrom + "') < '" + monthTo + "' ");
			sql.Append("  ) ,");
			sql.Append("   allWorkDay AS ( ");
			sql.Append("                        SELECT DISTINCT work_day ");
			sql.Append("                        FROM   T_REPORT_CYCLETIME_EEM_NEW trcen ");
			sql.Append("						   WHERE  trcen.ReceiveDate < '" + to + "' AND trcen.ReceiveDate >= '" + from + "' ");
			sql.Append("                    )  ");
			sql.Append(" SELECT main.MonthDate, ");
			sql.Append("		main.work_day WorkDay, ");
			sql.Append("		isnull(DATA.work_num,0) WorkNum ");
			sql.Append("	FROM   ( ");
			sql.Append("			SELECT work_day,MonthDate ");
			sql.Append("			FROM   dayShow ");
			sql.Append("				left join allWorkDay ON 1=1 ");
			sql.Append("			) main ");
			sql.Append("	LEFT JOIN DATA ");
			sql.Append("		 ON  main.MonthDate = DATA.ReceiveDate ");
			sql.Append("		 AND main.work_day = DATA.work_day  ");

			return
				Session.CreateSQLQuery(sql.ToString()).SetResultTransformer(Transformers.AliasToBean(typeof (CycleTimeNewReportDto)))
					.List<CycleTimeNewReportDto>();

		}

	}

}
